﻿Imports Entidades
Imports System.Data.OleDb
Public Class Datos

    Private conString As String '= "Provider= Microsoft.ACE.OLEDB.12.0;Data Source= " & My.Application.Info.DirectoryPath & "\Trueque.accdb"

    Public Sub New()
        conString = My.Settings.TruequeConnectionString ' My.MySettings.Default.TruequeConnectionString

    End Sub
    Public Function añadirCliente(ByVal cli As Cliente) As String

        Dim nombreApellidos = cli.NombreApellidos
        Dim telefono = cli.Telefono
        Dim email = cli.Email
        Dim idTipoCliente = cli.IdTipoCliente

        Dim sql As String = "INSERT INTO Cliente (NombreApellidos, Telefono, Email, IdTipoCliente) 
   VALUES (@nombreApellidos, @telefono, @email, @idTipoCliente)"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@nombreApellidos", nombreApellidos)
            comando.Parameters.AddWithValue("@telefono", telefono)
            comando.Parameters.AddWithValue("@email", email)
            comando.Parameters.AddWithValue("@idTipoCliente", idTipoCliente)

            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

   

    Public Function añadirEntrega(ByVal ent As Entrega) As String
        Dim idCliente = ent.IdCliente
        Dim fecha = Today.Date
        Dim numProductos = ent.NumeroProductos
        Dim idUsuario = ent.IdUsuario
        Dim valorada = False
        Using connection As New OleDbConnection(conString)

            Dim sql2 As String = "Select * From Cliente Where idCliente = @idCliente"
            Dim comando1 As New OleDbCommand(sql2, connection)
            comando1.Parameters.AddWithValue("@idCliente", ent.IdCliente)

            Try
                connection.Open()

                Using dr As OleDbDataReader = comando1.ExecuteReader

                    If Not dr.HasRows Then
                        Return "No existe este cliente"
                    End If
                    dr.Close()
                End Using
            Catch ex As Exception
                Return ex.Message

            End Try
            connection.Close()

            Dim sql As String = "INSERT INTO Entrega (IdCliente, Fecha, NumProductos, IdUsuario, Valorada) VALUES (@idCliente, @fecha, @numProductos, @idUsuario, @valorada)"
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@idCliente", idCliente)
            comando.Parameters.AddWithValue("@fecha", fecha)
            comando.Parameters.AddWithValue("@numProductos", numProductos)
            comando.Parameters.AddWithValue("@idUsuario", idUsuario)
            comando.Parameters.AddWithValue("@valorada", valorada)



            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function añadirUsuario(ByVal usu As Usuario) As String
        Dim nombre = usu.Nombre

        Dim sql As String = "INSERT INTO Usuario (Nombre) 
   VALUES (@nombre)"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@nombre", nombre)


            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function añadirAdministrador(ByVal admin As Administrador) As String
        Dim usuario = admin.Usuario
        Dim contraseña = admin.Contraseña

        Dim sql As String = "INSERT INTO Administrador (Usuario, Contraseña) 
   VALUES (@usuario, @contraseña)"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@usuario", usuario)
            comando.Parameters.AddWithValue("@contraseña", contraseña)


            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function añadirObjeto(ByVal obj As Objeto) As String
        Dim idFamilia = obj.IdFamilia
        Dim numeroFamilia = obj.NumeroFamilia
        Dim idEntrega = obj.IdEntrega
        Dim valoracion = obj.Valoracion
        Dim tamaño = obj.Tamaño
        Dim numeroSerie = obj.NumeroSerie
        Dim descripcion = obj.Descripcion



        Dim sql As String = "INSERT INTO Objeto (IdFamilia, NumeroFamilia, IdEntrega, Valoracion, Tamaño, NumeroSerie, Descripcion) 
   VALUES (@idFamilia, @numeroFamilia, @idEntrega, @valoracion, @tamaño, @numeroSerie, @descripcion)"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@idFamilia", idFamilia)
            comando.Parameters.AddWithValue("@numeroFamilia", numeroFamilia)
            comando.Parameters.AddWithValue("@idEntrega", idEntrega)
            comando.Parameters.AddWithValue("@valoracion", valoracion)
            comando.Parameters.AddWithValue("@tamaño", tamaño)
            comando.Parameters.AddWithValue("@numeroSerie", numeroSerie)
            comando.Parameters.AddWithValue("@descripcion", descripcion)



            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function EntregaValorada(ByVal ent As Entrega) As String
        Dim sql As String = "Update Entrega set valorada = @valorada where IdEntrega = @idEntrega"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@valorada", True)
            comando.Parameters.AddWithValue("@idEntrega", ent.IdEntrega)



            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No modificado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function





    'FUNCIONES ADMINISTRADOR

    Public Function ModificarObjeto(ByVal obj As Objeto, ByVal objAnt As Objeto) As String
        Dim idFamNue = obj.IdFamilia
        Dim numFamNue = obj.NumeroFamilia
        Dim Valoracion = obj.Valoracion
        Dim Tamaño = obj.Tamaño
        Dim NumeroSerie = obj.NumeroSerie
        Dim Descripcion = obj.Descripcion
        Dim DadoDeBaja = obj.dadoDeBaja
        Dim IdFamilia = objAnt.IdFamilia
        Dim NumeroFamilia = objAnt.NumeroFamilia
        Dim sql As String = "Update Objeto set IdFamilia = @idFamNue, NumeroFamilia = @numFamNue,  Valoracion = @valoracion, Tamaño = @tamaño, NumeroSerie = @numeroSerie, Descripcion = @descripcion, DadoDeBaja = @dadoDeBaja where IdFamilia = @idFamilia AND NumeroFamilia = @numeroFamilia "
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@idFamNue", idFamNue)
            comando.Parameters.AddWithValue("@numFamNue", numFamNue)
            comando.Parameters.AddWithValue("@valoracion", Valoracion)
            comando.Parameters.AddWithValue("@tamaño", Tamaño)
            comando.Parameters.AddWithValue("@numeroSerie", NumeroSerie)
            comando.Parameters.AddWithValue("@descripcion", Descripcion)
            comando.Parameters.AddWithValue("@dadoDeBaja", DadoDeBaja)
            comando.Parameters.AddWithValue("@idFamilia", IdFamilia)
            comando.Parameters.AddWithValue("@numeroFamilia", NumeroFamilia)



            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No modificado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function


    Public Function ModificarCliente(ByVal cli As Cliente) As String
        Dim nombre = cli.NombreApellidos
        Dim telefono = cli.Telefono
        Dim email = cli.Email
        Dim tipo = cli.IdTipoCliente
        Dim idCliente = cli.IdCliente


        Dim sql As String = "Update Cliente set NombreApellidos = @nombreApellidos, Telefono = @telefono, Email = @email,  idTipoCliente = @idTipoCliente where IdCliente = @idCliente"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@nombreApellidos", nombre)
            comando.Parameters.AddWithValue("@telefono", telefono)
            comando.Parameters.AddWithValue("@email", email)
            comando.Parameters.AddWithValue("@idTipoCliente", tipo)
            comando.Parameters.AddWithValue("@idCliente", idCliente)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No modificado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarCliente(ByVal cli As Cliente) As String

        Dim idCliente = cli.IdCliente


        Dim sql As String = "Delete From Cliente where IdCliente = @idCliente"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)

            comando.Parameters.AddWithValue("@idCliente", idCliente)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function ModificarAdministrador(ByVal admin As Administrador, ByVal adminAnt As Administrador) As String
        Dim usuarioNue = admin.Usuario
        Dim contNue = admin.Contraseña
        Dim usuarioAnt = adminAnt.Usuario
        Dim conAnt = adminAnt.Contraseña


        Dim sql As String = "Update Administrador set Usuario = @usuario, Contraseña = @contraseña where Usuario = @usuarioAnt AND Contraseña = @contraseñaAnt"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@usuario", usuarioNue)
            comando.Parameters.AddWithValue("@contraseña", contNue)
            comando.Parameters.AddWithValue("@usuarioAnt", usuarioAnt)
            comando.Parameters.AddWithValue("@contraseñaAnt", conAnt)






            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No modificado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarObjetosPorFamilia(ByVal fam As Familia) As String

        Dim id = fam.IdFamilia


        Dim sql As String = "Delete From Objeto where IdFamilia = @idFamilia"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)

            comando.Parameters.AddWithValue("@idFamilia", id)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function añadirFamilia(ByVal fam As Familia) As String

        Dim id = fam.IdFamilia
        Dim nom = fam.Nombre



        Dim sql As String = "INSERT INTO Familia (IdFamilia, Nombre) 
   VALUES (@id, @nombre)"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)
            comando.Parameters.AddWithValue("@id", id)
            comando.Parameters.AddWithValue("@nombre", nom)


            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No añadido"
                Else
                    Return ""
                End If
            Catch ex As Exception
                Return "Ya existe una familia con esa ID"
            End Try
            connection.Close()

        End Using
    End Function


    Public Function BorrarClientesPorTipo(ByVal tipo As TipoCliente) As String

        Dim id = tipo.id


        Dim sql As String = "Delete From Cliente where IdTipoCliente = @idTipo"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)

            comando.Parameters.AddWithValue("@idTipo", id)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function


    Public Function BorrarTrabajador(ByVal usu As Usuario) As String

        Dim id = usu.IdUsuario


        Dim sql As String = "Delete From Usuario where IdUsuario = @id"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)

            comando.Parameters.AddWithValue("@id", id)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return "No se puede borrar ya que hay entregas registradas por este trabajador"
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarFamilia(ByVal fam As Familia) As String

        Dim id = fam.IdFamilia



        Dim sql As String = "Delete From Familia where IdFamilia = @id"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)

            comando.Parameters.AddWithValue("@id", id)





            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return "No se puede borrar ya que hay objetos registrados de esta familia"
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarAdministrador() As String





        Dim sql As String = "Delete *  From Administrador"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)







            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarObjetos() As String





        Dim sql As String = "Delete *  From Objeto"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)







            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarEntregas() As String





        Dim sql As String = "Delete *  From Entrega"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)







            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarUsuarios() As String





        Dim sql As String = "Delete *  From Usuario"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)







            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function

    Public Function BorrarClientes() As String





        Dim sql As String = "Delete *  From Cliente"
        Using connection As New OleDbConnection(conString)
            Dim comando As New OleDbCommand(sql, connection)







            Try
                connection.Open()
                Dim nreg As Integer = comando.ExecuteNonQuery
                If nreg = 0 Then
                    Return "No borrado"
                Else

                    Return ""
                End If
            Catch ex As Exception
                Return ex.Message
            End Try
            connection.Close()

        End Using
    End Function
End Class
